<?php
namespace App\Models;

class Statistic extends BaseModel
{
    protected $payComplete = 20; // 已完成支付
    protected $start;
    protected $end;

    public function __construct()
    {
        $current = date('Y-m-d',time());
        $this->start = request('time_start',$current);
        $this->end = request('time_end',$current);
    }

    // 筛选shop_id条件
    protected function baseWhere($sid)
    {
        $baseWhere = [];
        if($sid){
            $baseWhere['shop_id'] = $sid;
        }
        if(!is_administrator()){
            $baseWhere['admin_id'] = plat_admin_id();
        }
        return $baseWhere;
    }

    // 获取店铺id
    protected function getSid()
    {
        if(session('shop_info')){
            $sid = session('shop_info.id');
        } else {
            $sid = request('shop_id', 0);
        }
        return $sid;
    }

    // 获取店铺的水桶品牌id数组
    protected function getBucketIds($sid)
    {
        if($sid) {
            $baseWhere = $this->baseWhere($sid);
            $shopBucketList = (new ShopBucketCategory())->_getList($baseWhere);
            $ids = [];
            if($shopBucketList){
                $shopBucketList->map(function($item) use (&$ids) {
                    collect(array_column($item['category_data'], 'id'))->map(function ($itemC) use (&$ids){
                        array_push($ids, $itemC);
                        return $ids;
                    });
                });
            }
            $tempIds = array_unique($ids);
        } else {
            $bucketList = (new BucketCategory())->_getList();
            $tempIds = $bucketList ? array_column($bucketList->toArray(), 'id') : [];
        }
        return $tempIds;
    }

    // 检测时间
    protected function checkTimeArea($start, $end){
        if($start && $end){
            if(strtotime($start) > strtotime($end)){
                return false;
            }
        }
        return true;
    }

    protected function paymentType($start, $end){
        if($start && !$end){
            $where['time'] = ['>',strtotime($start)];
        } else if($end && !$start){
            $where['time'] = ['<',strtotime("+1 day",strtotime($end))];
        } else if($start && $end){
            $where['time'] = ['between',[strtotime($start),strtotime("+1 day",strtotime($end)) -1]];
        } else {
            $where = [];
        }
        return $where;
    }

    // 水票统计
    public function waterBillStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $checkStatus = $this->checkTimeArea($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }

        $timeWhere = $this->paymentType($start, $end);
        $baseWhere = $this->baseWhere($sid);

        $result['final_sale_num'] = 0; // 购买数量
        $result['final_used_num'] = 0; // 消费数量
        $result['final_surplus_num'] = 0; // 剩余数量
        $result['data'] = []; // 水票信息数组

        // 查询导入水票销售信息
        $logWhere['description'] = '导入';
        $logWhere['pay_status'] = $this->payComplete;
        if($sid){
            $logWhere['shop_id'] = $sid;
        }
        $memberWaterBillLog = (new MemberWaterBillLog())->_getList($logWhere,'sum(buy_num) as total_num,waterbill_id',[],0,['waterbill_id']);
        if($memberWaterBillLog) {
            $result['final_sale_num'] += array_sum(array_column($memberWaterBillLog->toArray(),'total_num'));
        }
        $whereSale['order_sn'] = ['not_in',array_column($memberWaterBillLog->toArray(),'order_sn')]; // 排除查询水票时的可能重复的数据
        $logBillIds = array_column($memberWaterBillLog->toArray(), 'waterbill_id');

        // 查询水票的总销售信息
        $whereSale['pay_status'] = $this->payComplete;
        if($sid) {
            $whereSale['shop_id'] = $sid;
        }
        $totalSaleList = (new WaterBillOrder())->_getList($whereSale,'sum(total_num) as total_num,waterbill_id',[],0,['waterbill_id']);
        if($totalSaleList){
            $result['final_sale_num'] += array_sum(array_column($totalSaleList->toArray(), 'total_num'));
        }

        // 查询对应时间的水票销售信息
        if($timeWhere){
            $whereSale['payment_time'] = $timeWhere['time'];
        }
        unset($whereSale['order_sn']);
        $saleList = (new WaterBillOrder())->_getList($whereSale,'sum(total_num) as total_num,waterbill_id',[],0,['waterbill_id']);
        $saleBillIds = array_column($totalSaleList->toArray(),'waterbill_id');

        // 查询水票的消费数量（消费数量无时间筛选）
        $whereOrder['order.status'] = $this->payComplete;
        $whereOrder['order.bill_id'] = ['>', 0];
        if($sid) {
            $whereOrder['order.shop_id'] = $sid;
        }
        $totalOrderList = (new Order())->leftJoin('member_waterbill','order.bill_id', '=','member_waterbill.id')
            ->multiWhere($whereOrder)
            ->selectRaw("sum(bucket_num) as total_num,waterbill_id")
            ->groupBy('member_waterbill.waterbill_id')
            ->get();
        if($totalOrderList){
            $result['final_used_num'] += array_sum(array_column($totalOrderList->toArray(), 'total_num'));
        }

        // 筛选时间内消费数量
        if($timeWhere) {
            $whereOrder['order.payment_time'] = $timeWhere['time'];
        }
        $orderList = (new Order())->leftJoin('member_waterbill','order.bill_id', '=','member_waterbill.id')
            ->multiWhere($whereOrder)
            ->selectRaw("sum(bucket_num) as total_num,waterbill_id")
            ->groupBy('member_waterbill.waterbill_id')
            ->get();
        $orderBillIds = array_column($orderList->toArray(), 'waterbill_id');

        // 查询对应店铺的水票信息
        $baseWhere['id'] = ['in',array_unique(array_merge($logBillIds, $saleBillIds, $orderBillIds))];
        $waterBill = (new WaterBill())->_getList($baseWhere,'id,goods_name');

        if($waterBill) {
            // 水票信息补全
            $waterBill->map(function ($item) use ($memberWaterBillLog,$totalSaleList, $saleList, $totalOrderList, $orderList) {
                $item['total_sale_num'] = 0; // 单商品总销售量
                $item['total_use_num'] = 0; // 单商品总使用量
                $item['total_surplus_num'] = 0; // 单商品总剩余量
                $item['sale_num'] = 0;
                $item['use_num'] = 0;
                $memberWaterBillLog->map(function ($itemL) use (&$item) {
                    if($item['id'] == $itemL['waterbill_id']){
                        $item['total_sale_num'] += $itemL['total_num'];// 水票导入总销售量
                    }
                });
                $totalSaleList->map(function($itemTS) use (&$item){
                    if($item['id'] == $itemTS['waterbill_id']) {
                        $item['total_sale_num'] += $itemTS['total_num']; // 水票总销售数量
                    }
                });
                $saleList->map(function($itemS) use (&$item){
                    if($item['id'] == $itemS['waterbill_id']) {
                        $item['sale_num'] += $itemS['total_num']; // 一段时间内销售数量
                    }
                });
                $totalOrderList->map(function($itemTO) use (&$item) {
                    if($item['id'] == $itemTO['waterbill_id']) {
                        $item['total_use_num'] += $itemTO['total_num']; // 总使用量
                    }
                });
                $orderList->map(function($itemO) use (&$item) {
                    if($item['id'] == $itemO['waterbill_id']) {
                        $item['use_num'] += $itemO['total_num']; // 筛选时间内使用数量
                    }
                });
                $item['total_surplus_num'] += $item['total_sale_num'] - $item['total_use_num'];//剩余数量 = 总销售量 - 总使用量
            });

            $result['final_surplus_num'] = ($result['final_sale_num'] - $result['final_used_num'] < 0) ? 0 : ($result['final_sale_num'] - $result['final_used_num']);
            $result['data'] = $waterBill->toArray();
        }

        return json_encode($result);
    }

    // 配送统计 | 送水工数据统计
    public function workerDeliveryStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $checkStatus = $this->checkTimeArea($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }

        $timeWhere = $this->paymentType($start, $end);
        $baseWhere = $this->baseWhere($sid);

        // 工人送水数量统计
        $workerList = (new Worker())->_getList($baseWhere,'id,name');
        if($workerList) {
            $workerIds = array_column($workerList->toArray(),'id');

            // 查询送水工订单信息
            $whereBucket['worker_id'] = ['in', $workerIds];
            if($timeWhere){
                $whereBucket['addtime'] = $timeWhere['time'];
            }
            if($sid){
                $whereBucket['shop_id'] = $sid;
            }
            $orderFields = 'sum(send_num) as total_num,sum(bucket_num) as total_back_num,worker_id';
            $ordersList = (new BucketLog())->_getList($whereBucket,$orderFields,[],0,['worker_id']);
            $workerList->map(function ($item) use ($ordersList) {
                $item['total_num'] = 0;
                $item['total_back_num'] = 0;
                $ordersList->map(function ($itemO) use (&$item){
                    if($item['id'] == $itemO['worker_id']){
                        $item['total_num'] += $itemO['total_num'];
                        $item['total_back_num'] += $itemO['total_back_num'];
                    }
                });
                return $item;
            });
        }

        return json_encode($workerList->toArray());
    }

    // 配送统计 | 商品数据统计
    public function goodsDeliveryStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $checkStatus = $this->checkTimeArea($start, $end);
        $timeWhere = $this->paymentType($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }

        $goodsWhere['order.dispatch'] = 2;
        $goodsWhere['order.status'] = 20;
        if($sid){
            $goodsWhere['order_goods.shop_id'] = $sid;
        }
        if(!is_administrator()) {
            $goodsWhere['order_goods.admin_id'] = plat_admin_id();
        }
        if($timeWhere){
            $goodsWhere['order.addtime'] = $timeWhere['time'];
        }
        $orderGoodsList = (new OrderGoods())->leftJoin('order', 'order.id', '=', 'order_goods.order_id')
            ->multiWhere($goodsWhere)
            ->selectRaw("shop_goods_id,sum(goods_num) as total_num,goods_name")
            ->groupBy("order_goods.shop_goods_id")
            ->get();

        return json_encode($orderGoodsList->toArray());
    }

    // 配送统计 | 支付方式数据统计
    public function payTypeDeliveryStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $checkStatus = $this->checkTimeArea($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }
        $baseWhere = $this->baseWhere($sid);
        $timeWhere = $this->paymentType($start, $end);
        if($timeWhere){
            $baseWhere['addtime'] = $timeWhere['time'];
        }
        $baseWhere['status'] = 20;

        $result['total_online_num'] = 0; //微信支付
        $result['total_offline_num'] = 0; // 货到付款
        $result['total_bill_num'] = 0; // 水票支付
        $result['total_all_num'] = 0;
        $orderList = (new Order())->_getList($baseWhere);
        if($orderList) {
            $orderList->map(function ($item) use (&$result) {
                $result['total_all_num'] += $item['bucket_num'];
                // 水票支付
                if($item['bill_id'] > 0){
                    $result['total_bill_num'] += $item['bucket_num'];
                } else {
                    // 非水票支付
                    if($item['type'] == 0) {
                        $result['total_online_num'] += $item['bucket_num'];
                    } else {
                        $result['total_offline_num'] += $item['bucket_num'];
                    }
                }
            });
        }

        return json_encode($result);
    }

    // 配送统计 | 回桶之桶品牌数据统计
    public function shopBucketDeliveryStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $checkStatus = $this->checkTimeArea($start, $end);
        $timeWhere = $this->paymentType($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }

        $tempIds = $this->getBucketIds($sid);
        $whereOrder['order.dispatch'] = 2;
        $whereOrder['order_goods.bucket_category_id'] = ['in', $tempIds];
        if($sid) {
            $whereOrder['order_goods.shop_id'] = $sid;
        };
        if($timeWhere){
            $whereOrder['order.payment_time'] = $timeWhere['time'];
        }

        $orderList = (new OrderGoods())->leftJoin('order','order.id' ,'=', 'order_goods.order_id')
            ->multiWhere($whereOrder)
            ->selectRaw('sum(goods_num) as total_num,bucket_category_id')
            ->groupBy("order_goods.bucket_category_id")->get();

        $whereBucket['id'] = ['in', $tempIds];
        $bucketList = (new BucketCategory())->_getList($whereBucket,'id,title');
        if($bucketList){
            $bucketList->map(function ($itemB) use ($orderList){
                $itemB['total_num'] = 0;
                $orderList->map(function($itemO) use (&$itemB) {
                    if($itemO['bucket_category_id'] == $itemB['id']) {
                        $itemB['total_num'] += $itemO['total_num'];
                    }
                });
                return $itemB;
            });
        }

        return json_encode($bucketList->toArray());
    }

    // 水桶统计之桶品牌数据统计
    public function bucketDeliveryStatistic()
    {
        $sid = $this->getSid();
        $tempIds = $this->getBucketIds($sid);

        $whereData['bucket_category_id'] = ['in', $tempIds];
        if($sid) {
            $whereData['shop_id'] = $sid;
        }
        $whereCategory['id'] = ['in', $tempIds];

        $bucketData = (new BucketData())->_getList($whereData, 'bucket_category_id,bucket_num_in,bucket_num_out');
        $bucketCategory = (new BucketCategory())->_getList($whereCategory, 'id,title');

        $bucketData->map(function ($item) use ($bucketCategory) {
            $bucketCategory->map(function($itemB) use (&$item) {
                if($item['bucket_category_id'] == $itemB['id']) {
                    $item['title'] = $itemB['title'];
                }
            });
            return $item;
        });
        return json_encode($bucketData->toArray());
    }

    // 押金数据统计
    public function depositStatistic()
    {
        $sid = $this->getSid();
        $baseWhere = $this->baseWhere($sid);
        $result['total_bucket_cash'] = 0; // 水桶押金
        $result['total_water_deposit'] = 0; // 饮水机押金

        $memberCash = (new MemberCash())->_getList($baseWhere,'sum(cash) as total_cash,sum(water_dispenser_cash) as total_deposit');
        $memberCash->map(function($item) use (&$result) {
            $result['total_bucket_cash'] = bcadd($result['total_bucket_cash'], $item['total_cash'] ,2);
            $result['total_water_deposit'] = bcadd($result['total_water_deposit'], $item['total_deposit'] ,2);
            return $result;
        });
        return json_encode($result);
    }

    // 营业统计 | 水票/非水票数据统计
    public function revenueStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $timeWhere = $this->paymentType($start, $end);
        $checkStatus = $this->checkTimeArea($start, $end);
        if(!$checkStatus){
            $this->error = '开始时间不能大于结束时间';
            return false;
        }
        $result = [
            'data' => [],
            'total_bill' => 0, // 水票总金额
            'total_order' => 0, // 订单总金额
            'bill_num' => 0,// 水票总桶数
            'order_num' => 0 // 订单总桶数
        ];
        $baseWhere = $this->baseWhere($sid);
        $baseWhere['pay_status'] = $this->payComplete;// 已付款完成
        // 水票营收
        if($timeWhere){
            $baseWhere['payment_time'] = $timeWhere['time'];
        }
        $billFields = 'sum(order_amount) as bill_amount,sum(total_num) as bill_num,FROM_UNIXTIME(payment_time, "%Y-%m-%d") as pay_time';
        $billOrderList = (new WaterBillOrder())->_getList($baseWhere, $billFields,[],0,['pay_time']);
        $billOrderList->map(function($itemB) use (&$result){
            $payment_time = $itemB['pay_time'];
            $result['total_bill'] = bcadd($result['total_bill'], $itemB['bill_amount'],2);
            $result['bill_num'] += $itemB['bill_num'];
            if(isset($result['data'][$payment_time]['billOrder'])) {
                $result['data'][$payment_time]['billOrder'] = bcadd($result['data'][$payment_time]['billOrder'],$itemB['bill_amount'],2);
            } else {
                $result['data'][$payment_time]['billOrder'] = $itemB['bill_amount'];
            }
            if(!isset($result[$payment_time]['order'])) {
                $result['data'][$payment_time]['order'] = '0';
            }
        });

        // 非水票营收
        unset($baseWhere['pay_status']);
        $baseWhere['status'] = $this->payComplete;
        $baseWhere['bill_id'] = 0;
        $orderFields = 'sum(order_amount) as total_order,sum(bucket_num) as order_num,FROM_UNIXTIME(payment_time, "%Y-%m-%d") as pay_time';
        $orderList = (new Order())->_getList($baseWhere, $orderFields,[],0,['pay_time']);
        $orderList->map(function($itemO) use (&$result){
            $payment_time = $itemO['pay_time'];
            $result['total_order'] = bcadd($result['total_order'], $itemO['total_order'],2);
            $result['order_num'] += $itemO['order_num'];
            if(!isset($result['data'][$payment_time]['billOrder'])) {
                $result['data'][$payment_time]['billOrder'] = '0';
            }
            if(isset($result['data'][$payment_time]['order'])) {
                $result['data'][$payment_time]['order'] = bcadd($result['data'][$payment_time]['order'],$itemO['total_order'],2);
            } else {
                $result['data'][$payment_time]['order'] = $itemO['total_order'];
            }
        });
        ksort($result['data']);
        return json_encode($result);
    }

    // 营收统计 | 款项支付方式/款项分类 数据统计
    public function moneyStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $timeWhere = $this->paymentType($start, $end);

        $result = [
            'total_bill' => 0, // 水票款统计
            'total_order' => 0,// 订单款统计
            'total_online' => 0,//微信支付
            'total_offline' => 0,//货到付款
            'data' => [] // 微信支付/货到付款统计
        ];
        $baseWhere = $this->baseWhere($sid);
        if($timeWhere){
            $baseWhere['payment_time'] = $timeWhere['time'];
        }

        // 水票充值
        $baseWhere['pay_status'] = $this->payComplete;
        $billFields = 'sum(order_amount) as total_amount,FROM_UNIXTIME(payment_time, "%Y-%m-%d") as pay_time,type';
        $billOrder = (new WaterBillOrder())->_getList($baseWhere,$billFields,[],0,['pay_time','type']);
        $billOrder->map(function ($itemB) use (&$result) {
            $payment_time = $itemB['pay_time'];
            $online = $itemB['type'] == 0 ? $itemB['total_amount'] : 0; // 线上
            $offline = $itemB['type'] == 0 ? 0 : $itemB['total_amount']; // 线下

            $result['total_online'] = bcadd($result['total_online'], $online, 2);
            $result['total_offline'] = bcadd($result['total_offline'], $offline, 2);
            $result['total_bill'] = bcadd($result['total_bill'], $itemB['total_amount'], 2);
            $result['data'][$payment_time]['online'] = isset($result['data'][$payment_time]['online']) ? bcadd($result['data'][$payment_time]['online'],$online,2) : $online;
            $result['data'][$payment_time]['offline'] = isset($result['data'][$payment_time]['offline']) ? bcadd($result['data'][$payment_time]['offline'],$offline,2) : $offline;
        });

        // 零售订单
        unset($baseWhere['pay_status']);
        $baseWhere['status'] = $this->payComplete; // 已付款的订单
        $baseWhere['bill_id'] = 0; // 排除水票购买的订单
        $orderFields = 'sum(order_amount) as total_amount,FROM_UNIXTIME(payment_time, "%Y-%m-%d") as pay_time,type';
        $order = (new Order())->_getList($baseWhere,$orderFields,[],0,['pay_time','type']);
        $order->map(function ($itemO) use (&$result) {
            $payment_time = $itemO['pay_time'];
            $online = $itemO['type'] == 0 ? $itemO['total_amount'] : 0;
            $offline = $itemO['type'] == 0 ? 0 : $itemO['total_amount'];

            $result['total_online'] = bcadd($result['total_online'], $online, 2);
            $result['total_offline'] = bcadd($result['total_offline'], $offline, 2);
            $result['total_order'] = bcadd($result['total_order'], $itemO['total_amount'], 2);
            $result['data'][$payment_time]['online'] = isset($result['data'][$payment_time]['online']) ? bcadd($result['data'][$payment_time]['online'],$online,2) : $online;
            $result['data'][$payment_time]['offline'] = isset($result['data'][$payment_time]['offline']) ? bcadd($result['data'][$payment_time]['offline'],$offline,2) : $offline;
        });

        ksort($result['data']); // 按日期从升序排序
        return json_encode($result);
    }

    // 营收统计 | 水票款/订单款 细分商品统计
    public function goodsStatistic()
    {
        $sid = $this->getSid();
        $start = $this->start;
        $end = $this->end;
        $timeWhere = $this->paymentType($start, $end);

        $result = [];
        // 订单款之细分商品
        $orderWhere['order.status'] = $this->payComplete;
        $orderWhere['order.bill_id'] = 0;
        if($sid){
            $orderWhere['order_goods.shop_id'] = $sid;
        }
        if(!is_administrator()){
            $orderWhere['order_goods.admin_id'] = plat_admin_id();
        }
        if($timeWhere){
            $orderWhere['order.payment_time'] = $timeWhere['time'];
        }

        $fields = "goods_name,shop_goods_id,sum(goods_num*goods_price) as total_amount";
        $orderList = (new OrderGoods())->leftJoin('order', 'order_goods.order_id','=','order.id')
            ->multiWhere($orderWhere)
            ->selectRaw($fields)
            ->multiGroup(['shop_goods_id'])
            ->get();
        $orderList->map(function ($itemO) use (&$result) {
            $shop_goods_id = $itemO['shop_goods_id'];
            $amount = $itemO['total_amount'];
            if(isset($result[$shop_goods_id])) {
                $result[$shop_goods_id]['total_order_amount'] = bcadd($result[$shop_goods_id]['total_order_amount'], $amount, 2);
            } else {
                $result[$shop_goods_id] = [
                    'goods_name' => $itemO['goods_name'],
                    'shop_goods_id' => $itemO['shop_goods_id'],
                    'total_order_amount' => $amount,
                    'total_bill_amount' => 0
                ];
            }
        });

        // 水票款之细分商品
        $baseWhere = $this->baseWhere($sid);
        $baseWhere['pay_status'] = $this->payComplete;
        if($timeWhere){
            $baseWhere['payment_time'] = $timeWhere['time'];
        }
        $billFields = 'shop_goods_id,goods_name,sum(order_amount) as total_amount,payment_time,shop_id';
        $billOrderList = (new WaterBillOrder())->_getList($baseWhere,$billFields,[],0,['shop_goods_id']);
        $billOrderList->map(function ($itemB) use (&$result) {
            $shop_goods_id = $itemB['shop_goods_id'];
            if(isset($result[$shop_goods_id])) {
                $result[$shop_goods_id]['total_bill_amount'] = bcadd($result[$shop_goods_id]['total_bill_amount'], $itemB['total_amount'], 2);
            } else {
                $result[$shop_goods_id] = [
                    'goods_name' => $itemB['goods_name'],
                    'shop_goods_id' => $itemB['shop_goods_id'],
                    'total_order_amount' => 0,
                    'total_bill_amount' => $itemB['total_amount']
                ];
            }
        });

        return json_encode($result);
    }

}